package com.knife.database.generate;

import com.knife.common.utils.KnifeStringUtil;
import com.knife.common.utils.StopWatchUtil;
import com.knife.database.backup.concurrent.KnifeDbBackThreadPool;
import com.knife.database.common.constants.KnifeJdbcEnv;
import com.knife.database.common.utils.OracleJdbcUtil;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.StopWatch;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.Future;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.regex.Pattern;

/**
 * 根据业务给的文档，生成数据库表创建语句
 * CURRENT_PM_IDCURRENT_PM_ID
 *
 * @author： 76875
 * @date： 2022/7/22 星期五 17:09
 * @description：
 * @modifiedBy：
 * @version: 1.0
 */
public class DataBaseTableGenerateTest extends OracleJdbcUtil
        implements BaseTableColumn {


    public void alterTableAddColumn() throws Exception {

        // --------------------------
        // 提供待转化数据
        // --------------------------
        List<Column> allNewColumns = supplierColumnsV1();
        String tableName = "CMS_CONTRACT";

        // --------------------------
        // 以下内容不需要动
        // --------------------------

        String tableColumnSql = "select column_name from user_tab_cols where table_name='" + tableName + "'";
        Connection connection = getConnection(KnifeJdbcEnv.Uat);
        Statement statement = getStatement(connection);
        try {
            ResultSet resultSet = statement.executeQuery(tableColumnSql);
            Set<String> tableColumnSet = new HashSet<>();
            while (resultSet.next()) {
                String columnName = resultSet.getString(1);
                tableColumnSet.add(columnName);
            }
            // 对比数据，取出要新加的
            for (Column allNewColumn : allNewColumns) {
                if (!tableColumnSet.contains(allNewColumn.getName())) {
                    // 默认列可以为空
                    System.out.println("ALTER TABLE " + tableName + " ADD " + allNewColumn.getName() + " " + allNewColumn.getType() + ";");
                    System.out.println("COMMENT ON COLUMN " + tableName + "." + allNewColumn.getName() + " IS '" + allNewColumn.getComment() + "';");
                }
            }
            System.out.println("-------------以下是自定义文档标记-----------------");
            // 对比数据，取出要新加的属性，用来给文档标记
            for (Column allNewColumn : allNewColumns) {
                if (!tableColumnSet.contains(allNewColumn.getName())) {
                    System.out.println(KnifeStringUtil.underlineToCamelhump(StringUtils.lowerCase(allNewColumn.getName())));
                }
            }
        } finally {
            connection.close();
            statement.close();
        }

    }

    private String covertJavaTypeToSqlType(String javaType) {
        if (javaType.equalsIgnoreCase("string")) {
            return "VARCHAR(255)";
        } else if (javaType.equalsIgnoreCase("date")) {
            return "DATE";
        } else if (javaType.equalsIgnoreCase("integer")) {
            return "NUMBER";
        } else if (javaType.equalsIgnoreCase("decimal")) {
            return "NUMBER";
        } else if (javaType.equalsIgnoreCase("double")) {
            return "NUMBER";
        } else {
            return "VARCHAR(255)";
        }
    }

    /**
     * 提供形如下面的数据转化，需要注意的是，这里不会自动添加主键列：
     * contractCode	合同编号	是	string
     */
    private List<Column> supplierColumnsV1() {
        // --------------------------
        // 提供待转化数据
        // --------------------------

        /**
         * contractCode	合同编号	是	string
         */
        String columnStr = "contractCode\t合同编号\t是\tstring\n" +
                "contractVersion\t合同版本\t是\tstring\n" +
                "contractName\t合同名称\t是\tstring\n" +
                "contractStatus\t合同状态\t是\tstring\n" +
                "contractType\t合同类型\t是\tstring\n" +
                "contractKind\t计价方式\t是\tstring\n" +
                "hrUnitId\t业务归属部门\t是\tstring\n" +
                "customerId\t客户编号\t是\tstring\n" +
                "customerEmployee\t客户负责人\t是\tstring\n" +
                "customerEmployeePhone\t客户负责人联系电话\t否\tstring\n" +
                "saleEmployeeId\t销售员\t否\tstring\n" +
                "belongType\t承接类型\t是\tstring\n" +
                "url\t合同资信系统跳转链接\t是\tstring\n" +
                "contractDate\t签订日期\t是\tdate\n" +
                "contractNature\t合同性质\t是\tstring\n" +
                "contractSource\t合同来源\t是\tstring\n" +
                "originalVersion\t原合同版本\t否\tinteger\n" +
                "changeAmount\t变更金额\t否\tdecimal\n" +
                "oriAmount\t原合同金额\t否\tdecimal\n" +
                "changeReason\t变更原因\t否\tstring\n" +
                "removeType\t解除方式\t否\tstring\n" +
                "valuationMode\t计价方式\t是\tstring\n" +
                "paymentDirection\t收支方向\t是\tstring\n" +
                "startTime\t开始日期\t是\tdate\n" +
                "endTime\t结束日期\t是\tdate\n" +
                "periodExplain\t期限说明\t否\tstring\n" +
                "isAllCellect\t是否全额预收/付\t是\tstring\n" +
                "contractAmount\t合同金额\t是\tdecimal\n" +
                "exchangeRate\t汇率\t是\tdecimal\n" +
                "rmbAmount\t折算人民币金额\t是\tdecimal\n" +
                "amountNet\t不含税金额\t是\tdecimal\n" +
                "buzArea\t所属业务板块\t是\tstring\n" +
                "buzAreaId\t所属业务板块编号\t是\tstring\n" +
                "executorName\t执行人\t是\tstring\n" +
                "executorAccount\t执行人编号\t是\tstring\n" +
                "isShare\t是否共享\t是\tstring\n" +
                "remark\t备注\t否\tstring\n" +
                "creatorAccount\t创建人编号\t是\tstring\n" +
                "creatorName\t创建人\t是\tstring\n" +
                "createDeptCode\t创建人部门编号\t是\tstring\n" +
                "createDeptName\t创建人部门\t是\tstring\n" +
                "contactInformation\t联系方式-手机号\t是\tstring\n" +
                "creationTime\t创建日期\t是\tdate\n" +
                "isText\t文本来源\t否\tstring\n" +
                "relatedTemplateName\t范本名称\t否\tstring\n" +
                "contractText\t合同正文\t是\tstring\n" +
                "otherAttach\t其他附件\t否\tstring\n" +
                "isElectron\t是否电子合同\t是\tstring\n" +
                "approveTime\t审批通过日期\t是\tdate\n" +
                "isSettle\t是否结算合同\t是\tstring\n" +
                "isProductOrder\t是否PO单\t否\tstring\n";

        // --------------------------
        // 以下转化无需调整
        // --------------------------

        ArrayList<Column> columns = new ArrayList<>();
        String[] split = columnStr.split("\n");
        for (int i = 0; i < split.length; i++) {
            String[] split1 = split[i].split("\t");
            // 转化驼峰为数据库列
            String s = KnifeStringUtil.camelhumpToUnderline(split1[0]);
            String upperCase = StringUtils.upperCase(s);
            // 初始化列，注意其中的必填项判断
            Column column = new Column(upperCase, "否".equals(split1[2]), covertJavaTypeToSqlType(split1[3]), split1[1]);
            columns.add(column);
            if (i == 0) {
                column.setPrimaryKey(true);
            }
        }
        return columns;
    }

    /**
     * 提供形如下面的数据转化，需要注意的是，这里会自动添加ID主键列：
     * contractCode	合同编号	是	string
     */
    private List<Column> supplierColumnsV3() {
        // --------------------------
        // 提供待转化数据
        // --------------------------

        /**
         * contractCode	合同编号	是	string
         */
        String columnStr = "contractCode\t合同编号\t是\tstring\n" +
                "contractPosition\t主体地位\t是\tstring\n" +
                "responsibility\t违约责任\t是\tstring\n";

        // --------------------------
        // 以下转化无需调整
        // --------------------------

        ArrayList<Column> columns = new ArrayList<>();
        Column primaryKey = new Column("ID", false, "NUMBER", "主键");
        primaryKey.setPrimaryKey(true);
        columns.add(primaryKey);
        String[] split = columnStr.split("\n");
        for (int i = 0; i < split.length; i++) {
            String[] split1 = split[i].split("\t");
            // 转化驼峰为数据库列
            String s = KnifeStringUtil.camelhumpToUnderline(split1[0]);
            String upperCase = StringUtils.upperCase(s);
            // 初始化列，注意其中的必填项判断
            Column column = new Column(upperCase, "否".equals(split1[2]), covertJavaTypeToSqlType(split1[3]), split1[1]);
            columns.add(column);
        }
        return columns;
    }

    /**
     * 提供形如下面的数据转化，需要注意的是，这里不会自动添加主键列：
     * PRJ_THIRD_PARTY_ID	NUMBER	N	主键
     */
    private List<Column> supplierColumnsV2() {
        // --------------------------
        // 提供待转化数据
        // --------------------------

        /**
         * PRJ_THIRD_PARTY_ID	NUMBER	N	主键
         */
        String columnStr = "RECORD_ID\tNUMBER\tN\t表ID，主键\n" +
                "PARENT_RECORD_ID\tNUMBER\tN\t关联prj_project_analysis_header主键\n" +
                "INVESTMENT_CONTENT\tVARCHAR2(240)\tN\t设备费用名称\n" +
                "INVESTMENT_AMOUNT\tNUMBER\tN\t投资金额\n" +
                "REMARK\tVARCHAR2(1000)\tY\t备注\n";

        // --------------------------
        // 以下转化无需调整
        // --------------------------

        ArrayList<Column> columns = new ArrayList<>();
        String[] split = columnStr.split("\n");
        for (int i = 0; i < split.length; i++) {
            String[] split1 = split[i].split("\t");
            // 初始化列，注意其中的必填项判断
            Column column = new Column(split1[0], "Y".equals(split1[2]), split1[1], split1[3]);
            columns.add(column);
            if (i == 0) {
                column.setPrimaryKey(true);
            }

        }
        return columns;
    }


    /**
     * 根据业务文档初始化表sql
     */
    public void generateInitTableSql() {
        // ----------------------------------------------------
        // 待维护数据
        // ----------------------------------------------------
        // todo 表名
        String tableName = "PRJ_PROJECT_ANALYSIS_LINE".toUpperCase(Locale.ROOT);
        // todo 表描述
        String tableDesc = "可行性分析行表";
        // todo 业务给的字段描述
        List<Column> columns = supplierColumnsV2();


        // ----------------------------------------------------
        // 以下代码无需修改
        // ----------------------------------------------------

        System.out.println("-------------建表生成----------------");
        String hh = "\n";
        String space = "  ";
        String comment_table = "COMMENT ON TABLE ";
        String comment_column = "COMMENT ON COLUMN";
        String single_quote = "'";
        String is = "IS";
        StringBuilder sb = new StringBuilder();

        sb.append("DROP TABLE ").append(tableName).append(combel).append(hh);

        sb.append("CREATE TABLE ").append(tableName).append(hh).append("(").append(hh);
        // 生成字段
        Iterator<Column> iterator = columns.iterator();
        while (iterator.hasNext()) {
            Column column = iterator.next();
            sb.append(space)
                    .append(column.getName())
                    .append(space)
                    .append(column.getType())
                    .append(space);

            if (column.getPrimaryKey() != null && column.getPrimaryKey()) {
                sb.append(space).append("PRIMARY KEY").append(space);
            }
            sb.append(column.getNull() ? "" : "NOT NULL");
            if (iterator.hasNext()) {
            }
            sb.append(",");
            sb.append(hh);
        }
        sb.append(initSql);
        sb.append(")").append(hh).append("/").append(hh);
        sb.append(comment_table).append(tableName).append(space).append(is).append(space).append(single_quote).append(tableDesc).append(single_quote).append(combel).append(hh);
        // 描述字段
        for (Column column : columns) {
            sb.append(space).append(comment_column).append(space).append(tableName).append(".").append(column.getName()).append(space).append(is).append(space).append(single_quote).append(column.getComment()).append(single_quote).append(combel).append(hh);
        }
        // 序列
        sb.append("-------------序列生成----------------\n");
        sb.append("CREATE SEQUENCE ").append(tableName).append("_S START WITH 10000 increment by 1;");
        System.out.println(sb.toString());
        System.out.println();
    }

    public static class Column {
        private String name;

        private Boolean isNull;

        private String type;

        private String comment;

        private Boolean primaryKey;

        public Column(String name, Boolean isNull, String type, String comment) {
            this.name = name;
            this.isNull = isNull;
            this.type = type;
            this.comment = comment;
        }

        public Boolean getPrimaryKey() {
            return primaryKey;
        }

        public void setPrimaryKey(Boolean primaryKey) {
            this.primaryKey = primaryKey;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public Boolean getNull() {
            return isNull;
        }

        public void setNull(Boolean aNull) {
            isNull = aNull;
        }

        public String getType() {
            return type;
        }

        public void setType(String type) {
            this.type = type;
        }

        public String getComment() {
            return comment;
        }

        public void setComment(String comment) {
            this.comment = comment;
        }
    }

    /**
     * 表备份sql语句生成,注意其中会忽略
     * 以如下开头的
     * QRTZ
     * ACT
     * SYS
     * WFL
     * DATA_PERMISSION
     * 以及下面的表
     * HITF_INTERFACE_LOG
     * DATABASECHANGELOG
     * DATABASECHANGELOGLOCK
     */
    public void copyAndBackUpTable() throws Exception {
        // --------------------
        // 获取所有表sql
        // --------------------
        String backUpSql = "SELECT table_name FROM user_tables ORDER BY TABLE_NAME";
        Connection connection = getConnection(KnifeJdbcEnv.Uat);
        Statement statement = getStatement(connection);
        try {
            ResultSet resultSet = statement.executeQuery(backUpSql);
            List<String> tableNameList = new ArrayList<>();
            while (resultSet.next()) {
                String columnName = resultSet.getString(1);
                tableNameList.add(columnName);
            }
            // 添加时间戳
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd_HHmm");
            String format = sdf.format(new Date());
            Pattern backTablePattern = Pattern.compile("^[\\S]*[0-9]+$");
            Pattern schedulerTablePattern = Pattern.compile("^QRTZ{1}[\\S]*");
            Pattern workFlowPattern1 = Pattern.compile("^ACT{1}[\\S]*");
            Pattern sysPattern = Pattern.compile("^SYS{1}[\\S]*");
            Pattern workFlowPattern2 = Pattern.compile("^WFL{1}[\\S]*");
            Pattern dataPermissionPattern = Pattern.compile("^DATA_PERMISSION{1}[\\S]*");
            TreeSet<String> ignoreTableSet = new TreeSet<>();
            ignoreTableSet.add("HITF_INTERFACE_LOG");
            ignoreTableSet.add("DATABASECHANGELOG");
            ignoreTableSet.add("DATABASECHANGELOGLOCK");
            // 忽略
            tableNameList.stream().filter(item ->
                    backTablePattern.matcher(item).matches()
                            || dataPermissionPattern.matcher(item).matches()
                            || schedulerTablePattern.matcher(item).matches()
                            || workFlowPattern1.matcher(item).matches()
                            || sysPattern.matcher(item).matches()
                            || workFlowPattern2.matcher(item).matches()).forEach(ignoreTableSet::add);
            ignoreTableSet.stream().sorted(Comparator.comparing(String::toString)).forEach(item -> {
                System.out.println("忽略表：" + item);
            });

            // 实际要备份的表
            for (String tableName : tableNameList) {
                if (!ignoreTableSet.contains(tableName)) {
                    String backUpTableSql = "create table  " + tableName + "_" + format + " as select * from " + tableName + ";";
                    System.out.println(backUpTableSql);
                }
            }
        } finally {
            connection.close();
            statement.close();
        }
    }

    /**
     * 获取表中的列
     *
     * @return 表名key, 表列为value
     */
    private TreeMap<String, TreeSet<String>> getTableColumns(Statement statement) throws Exception {
        String tableColumnSql = "SELECT TABLE_NAME,column_name FROM user_tab_columns";
        TreeMap<String, TreeSet<String>> cacheMap = new TreeMap<>();
        ResultSet resultSet = statement.executeQuery(tableColumnSql);
        while (resultSet.next()) {
            String tableName = resultSet.getString(1);
            String columnName = resultSet.getString(2);
            TreeSet<String> columns = cacheMap.computeIfAbsent(tableName, k -> new TreeSet<>());
            columns.add(columnName);
        }
        return cacheMap;
    }

    /**
     * 比较不同环境之间的表字段插件
     */
    public void compareTableColumns() throws Exception {
        StopWatch compareTableColumns = StopWatchUtil.createStarted("compareTableColumns");
        // --------------------
        // 获取所有表sql
        // --------------------
        Connection uatConnection = getConnection(KnifeJdbcEnv.Uat);
        Connection prodConnection = getConnection(KnifeJdbcEnv.Prod);
        Statement uatStatement = getStatement(uatConnection);
        Statement prodStatement = getStatement(prodConnection);
        try {
            ThreadPoolExecutor threadPoolExecutor = KnifeDbBackThreadPool.getThreadPoolExecutor();
            Future<TreeMap<String, TreeSet<String>>> uatTableColumnsTreeMapFuture = threadPoolExecutor.submit(() -> {
                // 获取测试环境
                TreeMap<String, TreeSet<String>> uatTableColumns = getTableColumns(uatStatement);
                return uatTableColumns;
            });

            Future<TreeMap<String, TreeSet<String>>> prodTableColumnsTreeMapFuture = threadPoolExecutor.submit(() -> {
                // 获取生产环境
                TreeMap<String, TreeSet<String>> prodTableColumns = getTableColumns(prodStatement);
                return prodTableColumns;
            });

            // 比对
            TreeMap<String, TreeSet<String>> uatTableColumns = uatTableColumnsTreeMapFuture.get();
            TreeMap<String, TreeSet<String>> prodTableColumns = prodTableColumnsTreeMapFuture.get();
            // 1. 缺失表
            Set<String> uatTables = uatTableColumns.keySet();
            Set<String> prodTables = prodTableColumns.keySet();
            TreeSet<String> uatPlusTables = new TreeSet<>();
            TreeSet<String> prodPlusTables = new TreeSet<>();
            uatTables.forEach(item -> {
                if (!prodTables.contains(item)) {
                    uatPlusTables.add(item);
                }
            });

            System.out.println("-------------------------------------------------------");
            System.out.println("----------------prod环境对比uat环境缺失表-----------------");
            System.out.println("-------------------------------------------------------");
            uatPlusTables.stream().sorted(Comparator.comparing(String::toString)).forEach(item -> {
                System.out.println("prod环境对比uat环境缺失表:" + item);
            });

            System.out.println("-------------------------------------------------------");
            System.out.println("----------------uat环境对比prod环境缺失表-----------------");
            System.out.println("-------------------------------------------------------");
            prodTables.forEach(item -> {
                if (!uatTables.contains(item)) {
                    prodPlusTables.add(item);
                }
            });
            prodPlusTables.stream().sorted(Comparator.comparing(String::toString)).forEach(item -> {
                System.out.println("uat环境对比prod环境缺失表:" + item);
            });
            // 2. 缺失字段

            System.out.println("---------------------------------------------------------");
            System.out.println("--------------------------字段对比------------------------");
            System.out.println("---------------------------------------------------------");
            uatTableColumns.entrySet().stream().sorted(Map.Entry.comparingByKey()).forEach(entry -> {
                TreeSet<String> uatColumns = Optional.ofNullable(entry.getValue()).orElse(new TreeSet<>());
                TreeSet<String> prodColumns = prodTableColumns.getOrDefault(entry.getKey(), new TreeSet<>());
                Collection<String> uatPlusCollection = CollectionUtils.removeAll(uatColumns, prodColumns);
                Collection<String> prodPlusCollection = CollectionUtils.removeAll(prodColumns, uatColumns);
                if (CollectionUtils.isNotEmpty(uatPlusCollection)) {
                    System.out.println("prod环境对比uat环境,表<" + entry.getKey() + ">缺失字段:" + String.join(",", uatPlusCollection));
                }
                if (CollectionUtils.isNotEmpty(prodPlusCollection)) {
                    System.out.println("uat环境对比prod环境,表<" + entry.getKey() + ">缺失字段:" + String.join(",", prodPlusCollection));
                }
            });
            StopWatchUtil.stopAndTiming(compareTableColumns);
        } finally {
            uatConnection.close();
            uatStatement.close();
            prodConnection.close();
            prodStatement.close();
        }
    }
}
